CREATE OR REPLACE
PACKAGE BODY download_source_pkg
AS
  FUNCTION get_object_type( p_type IN VARCHAR2 ) RETURN VARCHAR2
  IS
    l_object_type VARCHAR2(30);
  BEGIN
    CASE
      WHEN p_type = 'PACKAGE' THEN
        l_object_type := 'PACKAGE_SPEC';
      WHEN p_type = 'PACKAGE BODY' THEN
        l_object_type := 'PACKAGE_BODY';
      ELSE
        l_object_type := p_type;
    END CASE;

    RETURN l_object_type;

  END get_object_type;

  FUNCTION get_file_name
    ( p_object_name IN VARCHAR2,
      p_object_type IN VARCHAR2 ) RETURN VARCHAR2
  IS
    l_object_name VARCHAR2(30) := LOWER( p_object_name );
    l_extension VARCHAR2(10);
    l_filename VARCHAR2(150);
  BEGIN
    --package spec = .pks
    --package body = .pkb
    --function = .func
    --procedure = .prc
    --trigger = .trig
    --table = .tab
    --sequence = .seq
    --view = .vw
    --type spec = .typs
    --type body = .typb
    --materialized view = .mv
    --materialized view log = .mvl
    --default = .sql
    CASE
      WHEN p_object_type = 'PACKAGE' THEN
        l_extension := '.pks';
      WHEN p_object_type = 'PACKAGE BODY' THEN
        l_extension := '.pkb';
      ELSE  
        l_extension := '.sql';
    END CASE;
    
    l_filename := l_object_name || l_extension;
    
    RETURN l_filename;
  END get_file_name;

  FUNCTION get_ddl
    ( p_owner IN VARCHAR2,
      p_object_name IN VARCHAR2,
      p_object_type IN VARCHAR2 ) RETURN CLOB
  IS
    l_handle NUMBER;
    l_clob CLOB;
    l_transform_number NUMBER;
    l_object_type VARCHAR2(30);
  BEGIN
    
    l_object_type := get_object_type( p_object_type );

    l_handle := dbms_metadata.open( object_type => l_object_type );

    dbms_metadata.set_filter
      ( handle => l_handle,
        name => 'SCHEMA',
        value => p_owner );

    dbms_metadata.set_filter
      ( handle => l_handle,
        name => 'NAME',
        value => p_object_name );

    l_transform_number := dbms_metadata.add_transform
                            ( handle => l_handle,
                              name => 'DDL' );

    l_clob := dbms_metadata.fetch_clob
                ( handle => l_handle,
                  cache_lob => TRUE,
                  lob_duration => DBMS_LOB.SESSION );

    dbms_lob.write
      ( lob_loc => l_clob,
        amount => 1,
        offset => dbms_lob.getlength( l_clob ),
        buffer => '/' );

    dbms_metadata.close( handle => l_handle );

    RETURN l_clob;
  EXCEPTION
    WHEN others THEN  
      dbms_metadata.close( handle => l_handle );
      RAISE;
  END get_ddl;

  PROCEDURE write_to_file
    ( p_dir IN VARCHAR2,
      p_filename IN VARCHAR2,
      p_file IN CLOB )
  IS
    l_file_handle UTL_FILE.FILE_TYPE;
    l_loops INTEGER := 1;
    l_max_linesize NUMBER := 32767;
    l_start_position NUMBER := 1;
    l_source VARCHAR2(32767);
  BEGIN
    l_file_handle := utl_file.fopen
                      ( location => p_dir,
                        filename => p_filename,
                        open_mode => 'w',
                        max_linesize => l_max_linesize );
  
    LOOP
      l_source := SUBSTR( p_file, l_start_position, l_max_linesize );
      EXIT WHEN l_source IS NULL;
  
      utl_file.put_line
        ( file => l_file_handle,
          buffer => l_source );
  
      l_start_position := l_loops + ( l_max_linesize * l_loops );
      l_loops := l_loops + 1;
  
    END LOOP;
    utl_file.fclose( l_file_handle );

  EXCEPTION
    WHEN others THEN
      utl_file.fclose( l_file_handle );
      RAISE;
  END write_to_file;

  PROCEDURE get_ddl_write_to_file
    ( p_owner IN VARCHAR2,
      p_object_name IN VARCHAR2,
      p_object_type IN VARCHAR2,
      p_dir IN VARCHAR2 )
  IS
    l_clob CLOB;
    l_filename VARCHAR2(150);
  BEGIN
    l_clob := get_ddl
                ( p_owner => p_owner,
                  p_object_name => p_object_name,
                  p_object_type => p_object_type );

    l_filename := get_file_name
                    ( p_object_name => p_object_name,
                      p_object_type => p_object_type );

    write_to_file
      ( p_dir => p_dir,
        p_filename => l_filename,
        p_file => l_clob );

  END get_ddl_write_to_file;

END download_source_pkg;
/
show errors
